<?php defined( '_VALID_MOS' ) or die( 'Direct Access to this location is not allowed.' );

/* JoomLeague League Management and Prediction Game for Joomla!
 * @version $Id: JoomLeague.class.php 1197 2008-07-07 07:10:15Z julienv $
 * @package Joomleague
 * @copyright Copyright (C) 2007  JoomLeague
 * @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see _joomleague_license.txt
 *  
 * Homepage: http://www.joomleague.de
 * Support: htt://www.joomleague.de/forum/
 * 
 * This file is part of JoomLeague.
 *  
 * JoomLeague is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * Please note that the GPL states that any headers in files and
 * Copyright notices as well as credits in headers, source files
 * and output (screens, prints, etc.) can not be removed.
 * You can extend them with your own credits, though...
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 *
 * The "GNU General Public License" (GPL) is available at
 * http://www.gnu.org/copyleft/gpl.html.
*/ 

//class JoomLeague extends JLClass{
class JoomLeague extends JLClass{
  /**
   * project id
   *
   * @var int
   */
  var $id;
  /**
   * project name
   *
   * @var string
   */
  var $name;
  /**
   * league id
   *
   * @var int
   */
  var $league_id;
  /**
   * season id
   *
   * @var int
   */
  var $season_id; 
  /**
   * project admin
   *
   * @var int
   */
  var $joomleague_admin;
  /**
   * project editor
   *
   * @var int
   */
  var $joomleague_editor;
  /**
   * master project id.
   * the templates setting of this project will be used if not present in current project.
   * If left to 0, this project uses independant templates settings.
   *
   * @var int
   */
  var $master_template;
  /**
   * matchdcode of current round.
   * This is used for current results, standings, etc...
   *
   * @var int
   */
  var $current_round;
  /**
   * method used for current round calculation
   *
   * @var int
   */
  var $current_round_auto;
  /**
   * delay used for current round calculation, in minutes.
   *
   * @var int
   */
  var $auto_time;
  /**
   * published state
   *
   * @var int
   */
  var $published;
  /**
   * project reference date
   *
   * @var string
   */
  var $start_date;
  /**
   * favorites teams id, separated by comma.
   *
   * @var string
   */
  var $fav_team;
  /**
   * color code for higlighting fav team
   *
   * @var string
   */
  var $fav_team_color;
  /**
   * Use legs in this project
   *
   * @var int
   */
  var $use_legs;
  /**
   * regular game length in minutes
   *
   * @var int
   */
  var $game_regular_time;
  /**
   * number of periods in regular games.
   *
   * @var int
   */
  var $game_parts;
  /**
   * length of half time break, in minutes.
   * used for global game length calculation
   *
   * @var int
   */
  var $halftime;
  /**
   * Allow overtime ?
   *
   * @var int
   */
  var $allow_add_time;
  /**
   * length of overtime in minutes
   *
   * @var int
   */
  var $add_time;
  /**
   * Point distribution for win/draw/loss for regular games.
   * Values separated by commas.
   *
   * @var int
   */
  var $points_after_regular_time; 
  /**
   * Point distribution for win/draw/loss for games decided in overtime.
   * Values separated by commas.
   *
   * @var int
   */
  var $points_after_add_time;
  /**
   * Point distribution for win/draw/loss for games decided after shootouts.
   * Values separated by commas.
   *
   * @var int
   */
  var $points_after_penalty;
  /**
   * Uses joomleague teams as referees ?
   *
   * @var int
   */
  var $teams_as_referees;
  /**
   * type of project
   *
   * @var unknown_type
   */
  var $project_type;
  /**
   * user lock id. 0 for non checked out project.
   *
   * @var int
   */
  var $checked_out;
  /**
   * ckecked out time
   *
   * @var string
   */
  var $checked_out_time;
  /**
   * template/extension to use.
   * lists folder from frontend com_joomleague/folder.
   *
   * @var string
   */
  var $template;
  /**
   * uses Fireboard integration
   *
   * @var int
   */
  var $enable_sb;
  /**
   * Fireboard category id for this project
   *
   * @var int
   */
  var $sb_catid;
  /**
   * server offset for this project.
   *
   * @var string.
   */
  var $serveroffset;

  function JoomLeague( &$db ) {
		$this->mosDBTable( '#__joomleague', 'id', $db );
    $this->init();
	}
  
  function init(){
    $this->current_round_auto = 0;
    $this->current_round = 1;
    $this->auto_time = '';
    $this->enable_sb = 0;        
    $this->template = 'default';
    $this->serveroffset = 0;
    $this->published = 1;        
	  $this->use_legs = 0;
    $this->game_regular_time = 90;
    $this->game_parts = 2;
    $this->halftime = 15;
    $this->points_after_regular_time = '3,1,0';
    $this->allow_add_time = 0;
    $this->points_after_add_time = '3,1,0';
    $this->points_after_penalty = '3,1,0';
    $this->teams_as_referees = 0; 
  }
   // Pagenavigation
function pagenav($jl_func,$rlimit, $current='',$user='', $mode='') {
    $mytask = mosGetParam($_REQUEST, 'mytask', false);;
    $divLevel = intval(mosGetParam($_REQUEST, 'divLevel', 0));
    $division = intval(mosGetParam($_REQUEST, 'division', 0));
    if ($current>=$rlimit-4)
    {
    	if ($current-3 <= 0) $startnavi=1; else $startnavi=$current-3;
    	$endnavi=$rlimit;
    }
    elseif ($current<=4)
    {
    	$startnavi=1;
    	$endnavi=$current+4;
    }
    else
    {
    	$startnavi=$current-3;
    	$endnavi=$current+4;
    }
  if ($endnavi>$rlimit) $endnavi=$rlimit;
	$url=JL_COMPONENT_LINK."&amp;func=".$jl_func."&amp;p=".$this->id;
    if ($user) $url .= "&amp;uid=".$user;
    if ($mode) $url .= "&amp;mode=".$mode;
    if ($mytask) $url .= "&amp;mytask=".$mytask;
    if ($division > 0) $url .= "&amp;division=".$division;
    if ($divLevel > 0) $url .= "&amp;divLevel=".$divLevel;
    $url .= JL_ITEMID_SUFFIX;
    $backward=$current-1;
    $forward=$current+1;
	if ($current!=1) {
		$prevlink="<a href='";
		$prevlink .= sefRelToAbs("$url&amp;r=$backward");
		$prevlink .= "#jl_top'>"._JL_PREV."</a>&nbsp;&nbsp;"; 
	}
	else $prevlink="";
  if ($current<=$rlimit-1) { 
		$nextlink="&nbsp;&nbsp;<a href='";
		$nextlink.= sefRelToAbs("$url&amp;r=$forward");
		$nextlink.="#jl_top'>"._JL_NEXT."</a>"; 
	} 
	else $nextlink="";
    $pageNav="<span class=\"pageNav\">";
	for ($counter = $startnavi;$counter<=$endnavi;$counter++) {
    	if ($counter<10) $pagenumber="0".$counter;
        else $pagenumber=$counter;
    	if ($counter<=$endnavi) {
        if ($counter!=$current) {
            	$pageNav.=" <a href=\"";
            	$pageNav.=sefRelToAbs($url.'&amp;r='.$counter);
            	$pageNav.="#jl_top\"> ";
          	}
        $pageNav.=" $pagenumber";
if ($counter!=$current) { $pageNav.="</a> "; }

      	}
  	}
  	$pageNav.="</span>";
	return $prevlink.$pageNav.$nextlink;
}
// Pagenavigation END

// Pagenavigation_dropdown
function pagenav2($jl_func, $rlimit, $current='',$user='', $mode='') {
    $divLevel = intval(mosGetParam($_REQUEST, 'divLevel', 0));
    $division = intval(mosGetParam($_REQUEST, 'division', 0));
    $pageNav2=<<<EOL
    <form action="" method="get" style="display:inline;">
    <select class="inputbox" onchange="joomleague_changedoc(this)">
EOL;
    
$url = JL_COMPONENT_LINK."&amp;func=".$jl_func."&amp;p=".$this->id;
    if ($user) $url .= "&amp;uid=".$user;
    if ($mode) $url .= "&amp;mode=".$mode;
    if ($division > 0) $url .= "&amp;division=".$division;
    if ($divLevel > 0) $url .= "&amp;divLevel=".$divLevel;
    $url .= JL_ITEMID_SUFFIX;
  for ($counter = 1;$counter<=$rlimit;$counter++)
  {
        if ($counter<10) $pagenumber="0".$counter;
        else $pagenumber=$counter;
    if ($counter<=$rlimit)
    {
            $pageNav2.="<option value='";
            $pageNav2.=sefRelToAbs($url.'&amp;r='.$counter)."'";
            if ($counter==$current) { $pageNav2.=" selected='selected'"; }
            $pageNav2.=">";
        }
        $pageNav2.="$pagenumber&nbsp;&nbsp;</option>";
    }
    $pageNav2.=<<<EOL
    </select></form>
EOL;
    return $pageNav2;
}
// Pagenavigation_dropdown ENDs




// roundsdropdown (mannweissjaniewof�rmansnochbraucht :-D 
function roundsdropdown($current='', $rlimit) {
    $rdd="<select class=\"inputbox\" name=\"set_r\">";
  for ($counter = 1;$counter<=$rlimit;$counter++)
  {
      if ($counter<10) $pagenumber="0".$counter;
    else $pagenumber=$counter;
    $rdd.="<option value=\"$counter\"";
    if ($counter==$this->current_round) $rdd.=" selected='selected'";
    $rdd.=">$pagenumber</option>";
  }
  $rdd.="</select>";
    return $rdd;
}
// roundsdropdown END 
  
  #returns the string
  function getColors($configcolors=''){
    
    $s = substr($configcolors,0,-1);
  
    $arr1 = array();
    if(trim($s) != ""){
      $arr1 = explode(";",$s);
    }
  
    $colors = array();
    
    $colors[0]["from"] = "";
    $colors[0]["to"] = "";
    $colors[0]["color"] = "";
    $colors[0]["description"] = "";
    
    for($i = 0; $i < count($arr1); $i++){
  
      $arr2 = explode(",",$arr1[$i]);
      if(count($arr2) != 4 ){
       break;
      }
      
      $colors[$i]["from"] = $arr2[0];
      $colors[$i]["to"] = $arr2[1];
      $colors[$i]["color"] = $arr2[2];
      $colors[$i]["description"] = $arr2[3];
    }
    
    return $colors;
  }
  
  function assignTeams($post = null){
    
    if(isset($post["teams"])){
      
      $query = "SELECT t.id 
                FROM #__joomleague_teams t, #__joomleague_team_joomleague tl
               WHERE t.id = tl.team_id AND tl.project_id = ".$this->id;
              
      $this->_db->setQuery($query);
      
      $arr1 = $this->_db->loadResultArray(); //old
      $arr2 = explode(",",$post["teams"]); //new
      
      $diff1 = array_diff($arr1,$arr2); // remove these Items
      $diff2 = array_diff($arr2,$arr1); // insert these Items
      
      #remove
      if(count($diff1) > 0){
                
        $list = sprintf("(%s)", implode(",",$diff1));
        // first check that the teams removed are not used in project games:
        $this->_db->setQuery(sprintf("SELECT count(*) FROM #__joomleague_matches WHERE project_id = %s AND (matchpart1 in %s OR matchpart2 in %s)" , $this->id, $list, $list));
        $res = $this->_db->loadResult();
        if ($res) {
          $msg = _JL_ADMIN_ASSIGN_TEAMS_UPDATE_FAILED_TEAMS_USED;
          return $msg;
        }
        
        // first remove the players of the teams
        $this->_db->setQuery(sprintf("DELETE FROM #__joomleague_playertool WHERE project_id = %s AND team_id in %s" , $this->id, $list));
        $this->_db->query();
        
        // then remove the teams
        $this->_db->setQuery(sprintf("DELETE FROM #__joomleague_team_joomleague WHERE project_id = %s AND team_id in %s" , $this->id, $list));
        $this->_db->query();
        //print $this->_db->getQuery();
      }
      #insert
      if(count($diff2) > 0){
        $query = "";
        foreach($diff2 as $v){
            $query .= sprintf("insert into #__joomleague_team_joomleague (project_id,team_id) values (%s,%s);\n",$this->id, $v);
        }
        
        $this->_db->setQuery($query);
        //print $this->_db->getQuery();
        $this->_db->query_batch(false);
        
        //print mysql_error(); 
      }
      return _JL_ADMIN_ASSIGN_TEAMS_UPDATE_OK;
    }
  }
  
  function copy(){
    $this->id = null;
    $this->name = "Kopie von ".$this->name;
    $this->published = 0;
    $this->project_tip = 0;
    if($this->_db->insertObject('#__joomleague', $this, 'id', true)) return mysql_insert_id();
    else print mysql_error();
    return false;    
  }
  
  function getTimestamp($date, $offset=0) {
    if ($date<>'') {
      $datum = split("-| |:",$date);
    } else {
      $datum = split("-| |:",mosFormatDate(date('Y-m-d H:i:s',time()), "%Y-%m-%d %H:%M:%S"));
    }
    if ($offset) {
      $serveroffset = split(":",$this->serveroffset);
      $timestampoffset = ($serveroffset[0]*3600) + ($serveroffset[1]*60);
    } else $timestampoffset = 0;
    $result = mktime($datum[3],$datum[4],$datum[5],$datum[1],$datum[2],$datum[0]) + $timestampoffset;
    return $result;
  }

  /**
   * return array of informations of teams in project
   *
   * @param string $ordering
   * @param int $division
   * @return array of team informations
   */
  function getTeams($ordering=null, $division=0){
    $teams = Array();
    
    $query = "SELECT tl.id AS teamtoolid, tl.division_id, tl.standard_playground, tl.admin, tl.start_points, tl.info, tl.team_id, tl.checked_out, tl.checked_out_time, tl.picture, tl.project_id,
                     t.id, t.name, t.short_name, t.middle_name, t.description, t.club_id, 
                     u.username, u.email, 
                     c.email as club_email, c.logo_small, c.country, c.website,
                     d.name AS division_name, d.shortname AS division_shortname, d.parent_id AS parent_division_id,
                     plg.name AS playground_name, plg.short_name AS playground_short_name
                FROM #__joomleague_team_joomleague tl
           LEFT JOIN #__joomleague_teams t on tl.team_id = t.id
           LEFT JOIN #__users u on tl.admin = u.id
           LEFT JOIN #__joomleague_clubs c on t.club_id = c.id
           LEFT JOIN #__joomleague_divisions d on d.id = tl.division_id
           LEFT JOIN #__joomleague_playgrounds plg on plg.id = tl.standard_playground
               WHERE tl.project_id = ".$this->id;
    if ($division > 0) $query .= " AND tl.division_id = ".$division;
    $query .= " ORDER BY ";
    if ($ordering) $query .= $ordering;
    else  $query .= "t.name";
               
    $this->_db->setQuery($query);
    if ( !$result = $this->_db->loadObjectList() ) $result = Array();
    return $result;
  }

    /**
   * return array of informations of teams in project, indexed by teamid
   *
   * @param string $ordering
   * @param int $division
   * @return array of team informations
   */
  function getTeamsIndexedById($ordering=null, $division=0) {
    $result = Joomleague::getTeams($ordering, $division);
    $teams = Array();
    
    if ( count($result) ) {
      foreach ($result as $r) {
  	    	$teams[$r->id]=$r;
  	    }
    }
    return $teams;
  }

  /**
   * return array of informations of teams used in specified games, indexed by teamid
   *
   * @param array $games Match info
   * @return array of team informations
   */
  function getTeamsFromMatches( &$games ){
    
  	if ( !count ($games) ) return Array();
    foreach ( $games as $m ) {
      $teamsId[] = $m->matchpart1;
      $teamsId[] = $m->matchpart2;
    }
    $listTeamId = implode( ",", array_unique($teamsId) );
    
    $query = "SELECT tl.id AS teamtoolid, tl.division_id, tl.standard_playground, tl.admin, tl.start_points, tl.info, tl.team_id, tl.checked_out, tl.checked_out_time, tl.picture, tl.project_id,
                     t.id, t.name, t.short_name, t.middle_name, t.description, t.club_id, 
                     u.username, u.email, 
                     c.email as club_email, c.logo_small, c.country, 
                     d.name AS division_name, d.shortname AS division_shortname,
                     p.name AS project_name
                FROM #__joomleague_team_joomleague tl
           LEFT JOIN #__joomleague_teams t on tl.team_id = t.id
           LEFT JOIN #__users u on tl.admin = u.id
           LEFT JOIN #__joomleague_clubs c on t.club_id = c.id
           LEFT JOIN #__joomleague_divisions d on d.id = tl.division_id
           LEFT JOIN #__joomleague p on p.id = tl.project_id
               WHERE t.id IN (".$listTeamId.")";
               
    $this->_db->setQuery($query);
    if ( !$result = $this->_db->loadObjectList() ) $teams = Array();
    
    if ( count($result) ) {
      foreach ($result as $r) {
  	    	$teams[$r->id]=$r;
  	    }
    }
    return $teams;
  }
  
  /**
   * returns project rounds in an array
   *
   * @param string $ordering ASC|DESC
   * @return array
   */
  function getRoundNames($ordering='ASC'){
   // added rond_date_first and round_date_last fields (Gucky 2007/05/25)
    $query = sprintf("SELECT distinct matchcode, id, name, round_date_first, round_date_last 
                        FROM #__joomleague_rounds 
                       WHERE project_id = %s ORDER BY matchcode $ordering", $this->id);
    
    $this->_db->setQuery($query);
    return $this->_db->loadObjectList();
  }
  
  /**
   * returns array of (id, round_date_first, round_date_last) according to parameters
   *
   * @param int $matchcode "to"
   * @param int $matchcodebegin "from"
   * @return array
   */
  function getRounds($matchcode, $matchcodebegin=1){
	  $last = intval(mosGetParam($_REQUEST, 'last', 0));
	  
	  if ($last > 0) {
      $lastvalue = $matchcodebegin-1+$matchcode-$last;
	  }
	  else $lastvalue = 0;
    // removed round_date from query, added rond_date_first and round_date_last fields (Gucky 2007/05/17)
    $query = "SELECT id, round_date_first, round_date_last
                FROM #__joomleague_rounds
               WHERE matchcode >= $matchcodebegin+$lastvalue AND matchcode <= $matchcode AND project_id = ".$this->id." 
            ORDER BY matchcode";
    $this->_db->setQuery($query);
    $result = $this->_db->loadResultArray();
    return $result;
  }
  
  //deleted all unnecessary information from query... other infos are now called by new Object where needed johncage 2007/08/16
  //added function parameter for ordering, it can now be called: getResults(1, "DESC") (getResultsDESC() is removed Gucky 2007/05/25
  function getResults($round = 0, $ordering="ASC", $unpublished=0){
  
  	$division = intval(mosGetParam($_REQUEST, 'division', 0));
	  $div_for_teams = array();
	  if ($division > 0) {
	    $query = "SELECT id FROM #__joomleague_divisions WHERE parent_id=$division";
	    $this->_db->setquery($query);
	    $div_for_teams = $this->_db->loadResultArray();
	  }
	  
    if ($round == 0) $round = $this->current_round;
    
    $query = sprintf("SELECT id
                FROM #__joomleague_rounds
               WHERE matchcode = %s AND project_id = %s", $round,$this->id);
              
    $this->_db->setQuery($query);
    $rounds = $this->_db->loadResultArray();
    
    $result = array();
    
    if(count($rounds) > 0){
    
      $roundlist = implode(",",$rounds);

	  // Zeile (m.alt_decision as decision,) in SQL-Abfrage eingef�gt --> stefan47110815 (23.04.2007)
    // added m.match_result_detail, round_date now will be received by matches-table to query Gucky 2007/05/25
    // added matchpart1_result_split. matchpart2_result_split for period results Gucky 2007/05/25
      $query = "SELECT matches.* FROM #__joomleague_matches AS matches";
      if ($division > 0) {
        $query .= " LEFT JOIN #__joomleague_team_joomleague AS tt ON (matches.matchpart1 = tt.team_id OR matches.matchpart2 = tt.team_id)";
      }
      $query .= " WHERE  matches.project_id = ".$this->id." AND 
                         matches.round_id in ($roundlist)";
      if ($unpublished!=1) $query .=" AND matches.published = 1";
      
      if ($division > 0) $query .= " AND (tt.division_id = ".$division;
      if (count($div_for_teams) > 0) $query .= " OR tt.division_id IN (".(implode(',', $div_for_teams)).")";
      if ($division > 0) $query .= ")";
      
      $query .= " GROUP BY matches.match_id";
      $query .= " ORDER BY matches.match_date $ordering, matches.match_number";
      $this->_db->setQuery($query);
      //print $this->_db->getQuery();
      $result = $this->_db->loadObjectList();
    }
    return $result;
  }

  /**
   * Returns rows of games info for results display
   *
   * @param Joomleague $project
   * @param int $round
   * @param int $division
   * @param array $config
   * @param int $teamid
   * @param string $ordering
   * @param int $unpublished
   * @return array rows objects
   */
  function getResultsRows($round = 0 , $division = 0, &$config, $teamId = 0, $ordering = 'ASC', $unpublished = 0 ) {
    $project = $this;
    //if ($round == 0) $round = $project->current_round;
    
    $result = array();
    
    $query_SELECT = "SELECT matches.*, r.matchcode as roundcode, r.name as roundname ";
    $query_FROM  = " FROM #__joomleague_matches AS matches
                       INNER JOIN #__joomleague_rounds AS r ON matches.round_id = r.id";
    $query_WHERE = " WHERE  matches.project_id = ".$project->id;
    if ($round) {
      $query_WHERE .= " AND r.matchcode = $round";
    }
    if ($teamId) {
      $query_WHERE .= " AND (matches.matchpart1 = $teamId OR matches.matchpart2 = $teamId)"; 
    }
    $query_END   = " GROUP BY matches.match_id
                       ORDER BY r.matchcode $ordering, matches.match_date $ordering, matches.match_number";
      
    if ($division > 0) {
      $query_FROM  .= " INNER JOIN #__joomleague_team_joomleague AS tt1 ON (matches.matchpart1 = tt1.team_id AND tt1.project_id = matches.project_id)
                        INNER JOIN #__joomleague_team_joomleague AS tt2 ON (matches.matchpart2 = tt2.team_id AND tt2.project_id = matches.project_id)
                        LEFT JOIN #__joomleague_divisions AS d1 ON tt1.division_id = d1.id
                        LEFT JOIN #__joomleague_divisions AS d2 ON tt2.division_id = d2.id";
      $query_WHERE .= " AND (  d1.id = $division OR d1.parent_id = $division  
                            OR d2.id = $division OR d2.parent_id = $division )";
    }
      
    if ($unpublished!=1) $query_WHERE .=" AND matches.published = 1";
     
    if ( $config['show_playground'] ) {
      $query_SELECT .= ", playground.name AS playground_name, playground.short_name AS playground_short_name";
      $query_FROM  .= " LEFT JOIN #__joomleague_playgrounds AS playground ON playground.id = matches.playground_id";
    }
    if ( $config['show_referee'] ) {
      if ( $project->teams_as_referees ) {
        $query_SELECT .= ", ref.name AS referee_name";
        $query_FROM  .= " LEFT JOIN #__joomleague_teams AS ref ON ref.id = matches.referee_id";
      }
      else {
        $query_SELECT .= ", ref.firstname AS referee_firstname, ref.lastname AS referee_lastname";
        $query_FROM  .= " LEFT JOIN #__joomleague_referees AS ref ON ref.id = matches.referee_id";
      }
    }
      
    $project->_db->setQuery( $query_SELECT.$query_FROM.$query_WHERE.$query_END );
    if ( !$result = $project->_db->loadObjectList() ) {
      echo $project->_db->getErrorMsg();
    }
    return $result;
  }
  
  //changed function getResults2 for case "showPlan" 2007/03/09
  //renamed function getResults2() to getResultsPlan() Gucky 2007/05/25
  //added function parameter for ordering, it can now be called: getResults(1, "DESC") (getResultsDESC() is removed Gucky 2007/05/25
  function getResultsPlan($round = 0, $team=0, $ordering='ASC',$referee=0, $getPlayground = 0, $getReferee = 0){
    $division = intval(mosGetParam($_REQUEST, 'division', 0));

    if ($division > 0) {
      $query = "SELECT id FROM #__joomleague_divisions WHERE parent_id=$division";
      $this->_db->setquery($query);
      $div_for_teams = $this->_db->loadResultArray();
      $div_for_teams[] = $division;
    }

    $query_SELECT = "SELECT  m.*, r.matchcode"; 
    $query_FROM = "     FROM #__joomleague_matches m
                        LEFT JOIN #__joomleague_rounds r ON m.round_id = r.id";
    $query_WHERE = " WHERE m.published = 1";
    
    if ($division > 0) {
      $query_FROM .= " LEFT JOIN #__joomleague_team_joomleague AS tt ON (m.matchpart1 = tt.team_id OR m.matchpart2 = tt.team_id)";
      $query_WHERE .= " AND tt.division_id IN (".(implode(',', $div_for_teams)).")";
    }
    
    if ($round > 0) {
      $query_WHERE .= " AND r.matchcode = '".$round."'";
    }
    
    //for referees, needs to check all project of current season
    if ($referee != 0) { 
      $query_SELECT .=", jl.name AS project_name";
      $query_FROM .=  " LEFT JOIN #__joomleague jl on jl.id = m.project_id";
      $query_WHERE .= " AND m.referee_id = '".$referee."' AND jl.season_id = '".$this->season_id."'";
    }
    // else, just the current project
    else $query_WHERE .= " AND m.project_id = '".$this->id."'";
    
    // only specified team calendar
    if ($team!=0) $query_WHERE .= sprintf(" AND (matchpart1 = %s or matchpart2 = %s)", $team, $team);
    
    $query_END = " GROUP BY m.match_id";
    $query_END .=" ORDER BY r.matchcode $ordering, m.match_date, m.match_number";

    if ( $getPlayground ) {
      $query_SELECT .= ", playground.name AS playground_name, playground.short_name AS playground_short_name";
      $query_FROM  .= " LEFT JOIN #__joomleague_playgrounds AS playground ON playground.id = m.playground_id";
    }
    if ( $getReferee ) {
      if ( $this->teams_as_referees ) {
        $query_SELECT .= ", ref.name AS referee_name";
        $query_FROM  .= " LEFT JOIN #__joomleague_teams AS ref ON ref.id = m.referee_id";
      }
      else {
        $query_SELECT .= ", ref.firstname AS referee_firstname, ref.lastname AS referee_lastname";
        $query_FROM  .= " LEFT JOIN #__joomleague_referees AS ref ON ref.id = m.referee_id";
      }
    }
      
    $this->_db->setQuery( $query_SELECT.$query_FROM.$query_WHERE.$query_END );
    //print $this->_db->getQuery();//exit();
    $result = $this->_db->loadObjectList();
    return $result;
  }

  // method to check starttimes of a requested round
  // return value is a bool, if there is only ONE Starttime for all matches of the round
  // it's used to remove Starttime column, if all matches start at the same time
  function getRoundMatchPeriod($round, &$FirstMatchDateTime, &$LastMatchDateTime){
    $query = "SELECT DISTINCT m.match_date 
                         FROM #__joomleague_matches AS m 
                        WHERE m.round_id = $round AND m.published = 1 AND m.project_id = $this->id 
                        ORDER BY m.match_date";
    $this->_db->setQuery($query);
    $result = $this->_db->loadObjectList();
    $bRet = false;
    if (isset($result) && is_array($result) && (count($result)>0))
    {
      $FirstMatchDateTime = $result[0]->match_date;
      $LastMatchDateTime  = $result[count($result)-1]->match_date;
      // Set return value to true, only exact one starttime exists
      if (count($result) == 1) $bRet = true;
    }
    else
    {
      $FirstMatchDateTime = "0000-00-00 00:00:00";
      $LastMatchDateTime  = "0000-00-00 00:00:00";
    }
    return $bRet;
  }
  
  //new function for displaying descending order moved to getResults and getResultsPlan with parameter!!! 
  
  function getTipResults($round = 0){
  // changed to get round_date from matches table Gucky 2007/05/25
    if($round == 0) $round = $this->current_round;
    
    $query = sprintf("SELECT id
                FROM #__joomleague_rounds
               WHERE matchcode = %s AND project_id = %s", $round,$this->id);
              
    $this->_db->setQuery($query);
    $rounds = $this->_db->loadResultArray();
    
    $result = array();
    
    if(count($rounds) > 0){
    
      $roundlist = implode(",",$rounds);
    
	// Zeile (m.alt_decision as decision,) in SQL-Abfrage eingef�gt --> stefan47110815 (23.04.2007)
    
        $query = sprintf("SELECT m.match_id,                  
                         m.matchpart1_result as e1,
                         m.matchpart2_result as e2,
                         m.alt_decision as decision,
                         m.matchpart1_result_decision AS v1,
                         m.matchpart2_result_decision AS v2,
                         m.round_id,
                         m.project_id,
                         tip.tip_home,
                         tip.tip_away,
                         tip.tip,
                         tip.joker,
                         u.username AS username,
                         u.id AS user_id
                   FROM #__joomleague_tip_results tip 
              LEFT JOIN #__joomleague_matches m on m.match_id = tip.match_id
              Left JOIN #__users u on u.id = tip.user_id
              LEFT JOIN #__joomleague_tip_members tm ON tm.user_id = tip.user_id AND tm.project_id = m.project_id
                  WHERE m.project_id = %s  
                    AND tm.approved = 1 
                    AND m.round_id IN (%s) 
               ORDER BY username DESC", $this->id,$roundlist);
      $this->_db->setQuery($query);
      //print $this->_db->getQuery();
      $result = $this->_db->loadObjectList();
      //print mysql_error();
    }
    return $result;
  }  
  /*
  function getPlan($team = 0){
  
    if($team == 0) return false;
    
	// Zeile (m.alt_decision as decision,) in SQL-Abfrage added --> stefan47110815 (23.04.2007)
    // added m.match_result_detail to query Gucky 2007/05/15
    // added m.matchpart1_result_split, matchpart2_result_split to query Gucky 2007/05/25
    // round_date now comes from matches table Gucky 2007/05/25
    $query = sprintf("SELECT  m.match_id, 
                              m.count_result,
                              t1.id as team_id1,
                              t1.name as home, 
                              t1.short_name as home_short,
                              t1.middle_name as home_middle,
                              t2.id as team_id2, 
                              t2.name as away, 
                              t2.middle_name as away_middle,
                              t2.short_name as away_short,
                              m.matchpart1_result as e1,
                              m.matchpart2_result as e2,
                              m.matchpart1_legs as l1,
                              m.matchpart2_legs as l2,
                              m.matchpart1_result_split,
                              m.matchpart2_result_split,
                              m.matchpart1,
                                m.matchpart2,
							  m.alt_decision as decision,
                              m.matchpart1_result_decision AS v1,
                              m.matchpart2_result_decision AS v2,
                              m.match_result_detail,                              
                              r.matchcode,
                              r.id AS round_id,
                              DATE_ADD(m.match_date, INTERVAL '".$this->serveroffset."' HOUR_MINUTE) as round_date
                        FROM #__joomleague_matches m
                   LEFT JOIN #__joomleague_teams t1 on t1.id = m.matchpart1
                   LEFT JOIN #__joomleague_teams t2 on t2.id = m.matchpart2
                   LEFT JOIN #__joomleague_rounds r on r.id = m.round_id
                       WHERE m.project_id = %s AND
                             m.published = 1 AND
                             (matchpart1 = %s or matchpart2 = %s) ORDER BY r.matchcode ASC", $this->id, $team, $team);
    $this->_db->setQuery($query);
    //print $this->_db->getQuery();
    $result = $this->_db->loadObjectList();
    //print mysql_error();
    return $result;
  }
  */
  function getTips($user, $round = 0){
  // round_date now comes from matches table Gucky 2007/05/25
    if($round == 0) $round = $this->current_round;
    
    $query = sprintf("SELECT id
                        FROM #__joomleague_rounds
                       WHERE matchcode = %s AND project_id = %s", $round,$this->id);
              
    $this->_db->setQuery($query);
    $rounds = $this->_db->loadResultArray();
    
    $roundlist = implode(",",$rounds);
    
	// Zeile (m.alt_decision as decision,) in SQL-Abfrage eingef�gt --> stefan47110815 (23.04.2007)

    $query = sprintf("SELECT m.match_id,
                             m.matchpart1, t1.name as home, 
                             m.matchpart2, t2.name as away, 
                             m.matchpart1_result as e1,
							 m.matchpart2_result as e2,
							 m.alt_decision as decision,
                             m.matchpart1_result_decision AS v1,
                             m.matchpart2_result_decision AS v2,
                             m.match_result_type as overtime,                             
							 u.username,
							 u.id AS user_id,
                             tip.id,
                             tip.tip_home, tip.tip_away, tip.joker,
                             tip.tip,
                             pp.point,
                             r.id AS round_id,
                             m.match_date as round_date
                        FROM #__joomleague_matches m
                   LEFT JOIN #__joomleague_tip_results tip on tip.match_id = m.match_id AND tip.user_id = %s
                   LEFT JOIN #__joomleague_predictiongame_points pp on tip.id = pp.tipp_id
                   LEFT JOIN #__joomleague_teams t1 on m.matchpart1 = t1.id
                   LEFT JOIN #__joomleague_teams t2 on m.matchpart2 = t2.id
                   LEFT JOIN #__joomleague_rounds r on r.id = m.round_id
                   LEFT JOIN #__users u on u.id = tip.user_id
                       WHERE m.project_id = %s AND m.published = 1 AND m.round_id in (%s)
                       ORDER BY m.match_date, match_id", 
                       $user->id, $this->id,  $roundlist);
    $this->_db->setQuery($query);
    //print $this->_db->getQuery();
    $result = $this->_db->loadObjectList();
    //print mysql_error();
    return $result;
  }
  
  function savePredictionPoints($match_id=0, $user_id=0, $project_id=0) {
    if ($match_id>0 || $project_id>0) {
      if ($match_id>0) $querywhere = ' WHERE tip.match_id = '.$match_id;
      if ($project_id>0) $querywhere = ' WHERE tip.project_id = '.$project_id;
      if ($user_id>0) $querywhere .= ' and tip.user_id='.$user_id;
      $this->getPredictionConfig();
      $query = "SELECT count(tip.id)
                  FROM #__joomleague_tip_results tip".$querywhere;
      $this->_db->setQuery($query);
      $zeilen = $this->_db->loadResult();
      $queryoffset = 2500;
      $query = "SELECT tip.id,
                       m.matchpart1_result as e1,
                       m.matchpart2_result as e2,
			           m.alt_decision as decision,
                       m.matchpart1_result_decision AS v1,
                       m.matchpart2_result_decision AS v2,
                       tip.project_id,
                       tip.user_id,
                       tip.tip_home,
                       tip.tip_away,
                       tip.tip,
                       tip.joker
                  FROM #__joomleague_tip_results tip 
             LEFT JOIN #__joomleague_matches m on m.match_id = tip.match_id".$querywhere."
                 LIMIT %s,%s";
      for($i=1;$i<=$zeilen; $i+=$queryoffset){
        $this->_db->setQuery(sprintf($query,$i-1,$queryoffset));
        $tipps = $this->_db->loadObjectList();
        foreach($tipps as $tipp) {
          if ($tipp->e1!='' || $tipp->e2!='' || $tipp->decision==1) {
            $tipp_points = new TipMachine($this);
            $tipp_points->addTipp($tipp);
            $query1 = sprintf("REPLACE #__joomleague_predictiongame_points SET 
                                      tipp_id = '%s',
                                      user_id = %s,
                                      point = %s,
                                      top = %s,
                                      diff = %s,
                                      tend = %s",
                                      $tipp->id, 
                                      $tipp->user_id,
                                      $tipp_points->points,
                                      $tipp_points->hitsCount,
                                      $tipp_points->diffCount,
                                      $tipp_points->tendCount);
            $this->_db->setQuery($query1);
		    $this->_db->query();
            unset($points);
          } else {
            $query1 = sprintf( "DELETE FROM #__joomleague_predictiongame_points WHERE tipp_id=%s",$tipp->id);
		    $this->_db->setQuery($query1);
		    $this->_db->query();
          }
        }
      }
    }
  }
  
  /**
   * new function increaseRound (with check if Round needs increasing)
   * set actual matchcode automatically now uses round_date_first or last game date
   * we could add a switch...
   *
   * @return int $current_round
   */
  function increaseRound ()
  { 
  	$current_date = mosFormatDate(date('Y-m-d H:i:s',time()), "%Y-%m-%d %H:%M:%S");
    switch ($this->current_round_auto) { 
    	case 0 : // manual mode
        return $this->current_round;
    	case 1 : // get current round from round_date_first
	        $query="SELECT matchcode FROM #__joomleague_rounds
	                WHERE project_id=$this->id 
	                AND ( round_date_first - INTERVAL ".($this->auto_time)." MINUTE < '$current_date' )
	                ORDER BY round_date_first DESC LIMIT 1";
	        break;
      case 2 : // get current round from round_date_last
          $query="SELECT matchcode FROM #__joomleague_rounds
                  WHERE project_id=$this->id 
                  AND ( round_date_last + INTERVAL ".($this->auto_time)." MINUTE > '$current_date' )
                  ORDER BY round_date_first ASC LIMIT 1";
          break;
      case 3 :
          // get current round from first game of the round
          $query="SELECT r.matchcode FROM #__joomleague_rounds AS r, #__joomleague_matches AS m
                  WHERE r.project_id=$this->id
                  AND m.round_id=r.id 
                  AND ( m.match_date - INTERVAL ".($this->auto_time)." MINUTE < '$current_date' )
                  ORDER BY m.match_date DESC LIMIT 1";
          break;
    	case 4 :
	        // get current round from last game of the round
	        $query="SELECT r.matchcode FROM #__joomleague_rounds AS r, #__joomleague_matches AS m
	                WHERE r.project_id=$this->id
	                AND m.round_id=r.id 
	                AND ( m.match_date + INTERVAL ".($this->auto_time)." MINUTE > '$current_date' )
	                ORDER BY m.match_date ASC LIMIT 1";
	        break;
    }
    $this->_db->setQuery($query);
    if ( $result = $this->_db->loadResult() ) {
      $current = $result;
      if ($this->current_round <> $current) {
        $query="UPDATE #__joomleague SET current_round=$current WHERE id=$this->id";
        $this->_db->setQuery($query);
        $this->_db->query();
        $this->current_round = $current;
      }
    }
    else {
//      echo $this->_db->getErrorMsg();
//      echo $this->_db->getQuery();
      $current = $this->current_round;
    }
    return $current;
  }

// new to get match events and show $sortedevents[] later in output Gucky 2007/05/15
	function getMatchEvents($matchid){
  	$query="SELECT * FROM #__joomleague_match_events WHERE match_id = $matchid
  					ORDER BY event_type_id, event_time";
  	$this->_db->setQuery($query);
  	$result = $this->_db->loadObjectList();
  	return $result;
  }
  
// changed getEventTypes() to also call single Event Details
// returns an object with eventType's id as key
	
  /**
   * returns an object with eventType's id as key
   *
   * @param int $event_type_id
   * @return array of events type objects
   */
  function getEventTypes($event_type_id=0)
  {
  	$query = " SELECT et.id AS etid, et.* 
  	           FROM #__joomleague_eventtypes AS et
  	           INNER JOIN #__joomleague_position_eventtype AS jpet ON et.id = jpet.eventtype_id
  	           INNER JOIN #__joomleague_pos_joomleague AS pj ON pj.position_id = jpet.position_id
  	           WHERE pj.project_id = ".$this->id;
  	
  	if ($event_type_id > 0) $query.=" AND et.id = $event_type_id";
  	$query.=" ORDER BY jpet.ordering ASC";
  	$this->_db->setQuery($query);
  	$result = $this->_db->loadObjectList("etid");
  	return $result;
  }

	/**
   * returns array of events indexed by their associated position
   *
   * @param int $positionId. if null returns all positions
   * @return array or false if no result
   */
  function getPositionEventTypes($positionId=0)
  {
    global $database;
    $query="SELECT pet.*, et.name, et.icon 
            FROM #__joomleague_position_eventtype AS pet
            INNER JOIN #__joomleague_eventtypes AS et ON et.id = pet.eventtype_id
            INNER JOIN #__joomleague_pos_joomleague AS pj ON pj.position_id = pet.position_id
            WHERE pj.project_id = ".$this->id;
    if ($positionId > 0) $query.=" AND pet.position_id = $positionId";
    $query.=" ORDER BY pet.ordering ASC";
    $database->setQuery($query);
    if ($result = $database->loadObjectList()) {
      if ($positionId) return $result;
      else {
	    	$posEvents=array();
	    	foreach ($result as $r) {
	      	$posEvents[$r->position_id][]=$r;
	      }
	      return($posEvents);
      }
    }
    else {
      return false;
    }
  }
  
// new to get match events and show $sortedevents[] later in output Gucky 2007/05/15 ends
// new function to receive the TeamtoolID Gucky 2007/05/25
  /**
   * Returns associated teamtool id 
   *
   * @param int $team_id
   * @return int
   */
	function getTeamtool_id( $team_id ) 
	{
    global $my;
		$query="SELECT id FROM #__joomleague_team_joomleague WHERE team_id = $team_id
		AND project_id = ".$this->id;
		$this->_db->setQuery($query);
		$result = $this->_db->loadResult();
		if ( $this->_db->getErrorNum() && $my->gid > 1 ) {
			echo $this->_db->getErrorMsg();
		}
	  return $result;
	}
	
	// changed function getEventsRanking() for faster assigning rank to players
	// in order to limit eventsRanking and make page navigation
	// player and team information are now collected in $htmlHelper->show_event_stats()
	// johncage 2007/08/15
	function getEventsRanking($id, $splitt=0, $direction='DESC', $limit=0, $limitstart=0)
	{
		// build query
		$query = ' SELECT SUM(me.event_sum) AS p, me.player_id as player_id, me.player_id2 as player_id2';
		if ($splitt==1) $query.=", me.notice";
		$query.= ' FROM #__joomleague_match_events me
                WHERE me.project_id =  '.$this->id.'
                  AND me.event_type_id =  '.$id.'  
             GROUP BY me.player_id, me.player_id2';
		if ($splitt==1) {
			$query .= ', me.notice ORDER BY notice '.$direction.', ';
		}
		else {
			$query .= ' ORDER BY ';
		}
		$query .= "p DESC";

		// get results
		$this->_db->setQuery($query);
		$rows = $this->_db->loadObjectList();

		$rank = 1;
		$rowcounter = 1;
		$lastpoints=0;
		$lastnotice=0;
		$newrow=array();
		$y=0;
		for ( $x=0; $x < count( $rows ); $x++ ) 
		{
			$row = $rows[$x];
			
			// compute rank
			if ($splitt==1) {
				if ( $lastnotice <> $row->notice ) {
					$rank = $rowcounter;
				} 
				else {
					if ($row->p < $lastpoints) $rank = $rowcounter;
				}
			}
			else if ($row->p < $lastpoints) {
			  $rank = $rowcounter;
			}
			
			if ( $x >= $limitstart )
			{
				$newrow[$y] = $row;
				$newrow[$y]->rank = $rank;
				
				$query="SELECT id FROM #__joomleague_playertool WHERE player_id=".$newrow[$y]->player_id." and project_id=".$this->id;
				$this->_db->setQuery($query);
				$newrow[$y]->player_id=$this->_db->loadResult();
				
				$query="SELECT id FROM #__joomleague_playertool WHERE player_id=".$newrow[$y]->player_id2." and project_id=".$this->id;
				$this->_db->setQuery($query);
				$newrow[$y]->player_id2=$this->_db->loadResult();
				
				$y++;
			}
			$lastpoints=$row->p;
			if ($splitt==1) {
        $lastnotice=$row->notice;
			}
			
			$rowcounter++;			
			if ( $limit && $y == $limit ) break;
		}
		return $newrow;
	}
	
	function getPlayerEvents($pid, $evid, $tid=0) {
		$query = "SELECT SUM( event_sum ) as anzahl
            FROM #__joomleague_match_events
           WHERE (player_id = $pid OR player_id2 = $pid) AND";
        if ($tid!=0) $query.=" team_id = $tid AND";
        $query.="           event_type_id = $evid AND 
           project_id = ".$this->id;
        $this->_db->setQuery($query);
		$result = $this->_db->loadResult();
		return $result;
	}
	
	
	/**
	 * return true if the user is editor for one of the team in the project
	 *
	 * @param int $user_id
	 * @return boolean
	 */
	function isTeamEditor( $round, $user_id ) {
    global $database;
		if ($user_id > 0) { 
		  $query = "SELECT tt.admin FROM #__joomleague_team_joomleague AS tt
		  					INNER JOIN #__joomleague_matches AS m ON (m.matchpart1 = tt.team_id OR m.matchpart2 = tt.team_id) AND m.project_id = tt.project_id
		            INNER JOIN #__joomleague_rounds AS r ON r.id = m.round_id AND r.project_id = m.project_id
		  					WHERE tt.project_id = '".$this->id."' 
		  						AND tt.admin='$user_id'
		  						AND r.matchcode = $round";
		  $database->setQuery($query);
		  if ( $database->loadResult() ) return true;
		}
	  return false;
	}
	

  function getMatchAdmin($tid) {
		$can_edit=false;
		$tempmatch=new Match ($this->_db);
    $tempmatch->load($tid);
    $ttid=$this->getTeamtool_id($tempmatch->matchpart1);
    $tempteamtool=new TeamTool($this->_db);
    $tempteamtool->load($ttid);
    return $tempteamtool->admin;;
  }
  
  /**
   * returns true if user is admin of one of the 2 teams involved in the game
   *
   * @param int $match_id
   * @param int $user_id
   * @return bool
   */
  function isMatchAdmin($match_id, $user_id) {
    global $database;
    
		if ( defined('JL_USER_IS_PROJECT_ADMIN') || defined('JL_USER_IS_PROJECT_EDITOR')
                || defined('JL_USER_IS_BIG_BOSS') )
    {
      return true;
    }
    
    $query = "SELECT COUNT(*) FROM #__joomleague_matches AS m
    					INNER JOIN #__joomleague_team_joomleague AS tt1 ON m.matchpart1 = tt1.team_id AND m.project_id = tt1.project_id
		          INNER JOIN #__joomleague_team_joomleague AS tt2 ON m.matchpart2 = tt2.team_id AND m.project_id = tt2.project_id
		          WHERE m.match_id=$match_id AND (tt1.admin = $user_id OR tt2.admin = $user_id)";
    $database->setQuery($query);
		return $database->loadResult();
  }
  
  /**
   * return games events
   *
   * @param Array object games
   * @return unknown
   */
  function jlmatchevents( &$matches ) 
  {		
  	$thissortedevents = array();
  	foreach ($matches AS $match)
    {
	  	$query = " SELECT me.*,
			                  p1.id AS player_id, p1.firstname AS player_firstname, p1.lastname AS player_lastname,
			                  p2.id AS player_id2, p2.firstname AS player2_firstname, p2.lastname AS player2_lastname
			           FROM #__joomleague_match_events AS me
			           INNER JOIN #__joomleague_players AS p1 ON p1.id = me.player_id
			           LEFT  JOIN #__joomleague_players AS p2 ON p2.id = me.player_id2
			           WHERE match_id = $match->match_id ";
	    $this->_db->setQuery($query);
	    $result = $this->_db->loadObjectList();
	    
	    if ( $result = $this->_db->loadObjectList() )
	    {
	    	foreach ( $result as $r )
	    	{
	    		$thissortedevents[$match->match_id][$r->event_type_id][$r->team_id][] = $r;
	    	}
	    	return $thissortedevents;
	    }
	    else {
	    	echo $this->_db->getErrorMsg();
	    }
    }
    return $thissortedevents;
	}
	
	/**
	 * returns associative array of pGoalsarameters values from specified template
	 *
	 * @param string $template name
	 * @return array
	 */
	function getTemplateConfig ($template) {
		$query = "SELECT params FROM #__joomleague_template_config WHERE template = '".$template."'
							AND project_id = $this->id";
		$this->_db->setQuery($query);
		if ($result=$this->_db->loadResult()) {
			$params = explode("\n", trim($result));
			foreach($params AS $param) {
				list ($name, $value)= explode("=", $param);
				$configvalues[$name]=$value;
			}
		}
		else if ($this->master_template) { //call recursively
		  $proj = new Joomleague($this->_db);
		  $proj->load($this->master_template);
		  return $proj->getTemplateConfig ($template);
    }
		else {
      echo "Template $template is missing, please visit templates menu in project admin...<br/>";
      return false;
    }
  
    // check some defaults and init data for quicker access
    switch ($template)
    {
      case "show_table.tpl":
      // showTable contains the setup of ranking order
      // it's ensured to have a valid ranking order and the data is copied to a real integer index array 
      // for faster access later
      
      // init ranking array with default values
      // for people updating, the tiebreaker won't be set until they edit 
      // show_table.tpl.xml. In that case, use a default sorting
      if ( !array_key_exists( 'tie_breaker_1',$configvalues ) ) { 
        $configvalues['rankingorder'][1] = 'points';
        $configvalues['rankingorder'][2] = 'goal_diff';
        $configvalues['rankingorder'][3] = 'goal_for';
        $configvalues['rankingorder'][4] = 'goal_against';
      }
      
      $j=1;
      //now extrakt the rankingorder to an integer indexed array
      while ( array_key_exists( 'tie_breaker_'.$j,$configvalues ) ) 
      {
        $configvalues['rankingorder'][$j] = $configvalues['tie_breaker_'.$j];
        $j++;
      }
      break;
      
    case "show_Tip_Ranking.tpl":
      // showTable contains the setup of tip ranking order
      if ( !array_key_exists( 'sort_order_1',$configvalues ) ) 
      // for people updating, the tiebreaker won't be set until they edit 
      //show_table.tpl.xml. In that case, use a default sorting 
      {
        $configvalues['sort_order_1'] = 'points';
        $configvalues['sort_order_2'] = 'correct_tips';
        $configvalues['sort_order_3'] = 'correct_diffs';
        $configvalues['sort_order_4'] = 'correct_tend';
        $configvalues['sort_order_5'] = 'count_tips_p';
      }
      break;
      
    default:
      break;
    }		
		return $configvalues;
	}
	
	// new function for collecting total count of players per event and project
	// johncage 2007/08/15
	//
	// TODO: this function really needs some comments !!!!
	//
	function getTotalEventPlayers( $eventtype, $splitt=0 ) 
	{
        global $my;
		if ( $splitt == 1 ) {
            $query = " SELECT COUNT(player_id) as count_player "; 
        }
        else {
            $query = " SELECT COUNT(DISTINCT(player_id)) as count_player ";
        }
        $query .= " FROM #__joomleague_match_events
		            WHERE project_id = ".$this->id."
        			AND event_type_id = ".$eventtype;        
        if ( $splitt == 1 ) {
            $query.=" GROUP BY notice, player_id";
        }
		$this->_db->setQuery($query);
		if ($splitt==1) {
			$totalplayers = count($this->_db->loadAssocList());
		}
		else {
			$totalplayers = $this->_db->loadResult();
		}
		if ( $this->_db->getErrorNum() && $my->gid > 1 ) {
			echo $this->_db->getErrorMsg();
		}
		return $totalplayers;
	}
	// new function for collecting total count of players per event and project ENDS
	
	/**
  * A simple function to get list of matches in the project
  * @return Match array
  */
	function getMatches() {

    //gets all matches rows in the project
    $query = "SELECT * from #__joomleague_matches
			WHERE project_id = ".$this->id;
		$this->_db->setQuery($query);
		$games = $this->_db->loadAssocList();
		
		//generate corresponding objects
		$result = array();
    foreach ($games as $game) {
      $match = new Match( $this->_db );
      $match->bind( $game );
      $result[] = $match;
    }
		return $result;
  }

  /**
  * A simple function to get list of ids of division in the project
  * @param divLevel int level of subdivision: 0 for all, 1 for first sublevel, 2 for 2nd sublevel...    
  * @return id int array, false if no divisions for this level
  */
	function getDivisionsId( $divLevel = 0 ) {

        
    //gets ids of divisions in the project
    $query = "SELECT id from #__joomleague_divisions
			WHERE project_id = ".$this->id;
		if ( $divLevel==1 ) $query .= " AND parent_id=0";
		else if ( $divLevel==2 ) $query .= " AND parent_id>0";
		$query .= " ORDER BY name";
		$this->_db->setQuery($query);
		return $this->_db->loadResultArray();
  }
	
	/**
  * A simple function to get list of templates of the project
  * @return Template objects array
  */
	function getTemplates() {

    //gets ids of templates in the project
    $query = "SELECT id from #__joomleague_template_config
			WHERE project_id = ".$this->id;
		$this->_db->setQuery($query);
		$ids = $this->_db->loadObjectList();
		
		//generate corresponding objects
		$result = array();
    foreach ($ids as $id) {
      $object = new TemplateConfig( $this->_db );
      $object->load( $id->id );
      $result[] = $object;
    }
		
		return $result;
  }

  /**
  * Overrides toXML function to output informations of all data 
  * relevant to a project (templates, teams, matchdays, matches)
  * @return string
  */
	function toXML( $mapKeysToText=false ){

    //$output = mosDBTable::toXML();
    
    //$output = $this->exportToXml("#__joomleague");
    $output = $this->exportToXml();
    
    foreach ($this->getTemplates() as $object) {
      //$output .= $object->toXML();
    }
    
    foreach ($this->getTeams() as $object) {
      $teamtool = new TeamTool( $this->_db );
      $teamtool->load($object->teamtoolid);
      
      $team = new JL_Team( $this->_db );
      $team->load($teamtool->team_id);
      $output .= "\n".$teamtool->exportToXml()."\n".$team->exportToXml();
      
      if ($team->club_id > 0) {
        $club = new Club( $this->_db );
        $club->load($team->club_id);
        $output .= "\n".$club->exportToXml();
      }
    }

    foreach ($this->getDivisionsId() as $object) {
      $div = new LeagueDivision( $this->_db );
      $div->load($object);
      $output .= "\n".$div->exportToXml();
    }
    
    foreach ($this->getRoundNames() as $object) {
      $round = new Round( $this->_db );
      $round->load($object->id);
      $output .= "\n".$round->exportToXml();
    }
    
    foreach ($this->getMatches() as $match) {
      $output .= "\n".$match->exportToXml();
    }
    
    return "<project>\n".$output."\n</project>";
  }
  function mark_now_playing($thistime, $match_stamp, &$config) {
    $whichpart = 1;
    $gone_since_begin = intval(($thistime - $match_stamp)/60);
    $parts_time = intval($this->game_regular_time / $this->game_parts);
    $temptext = 'SOMETHING WENT WRONG';
    for ($temp_count = 1; $temp_count <= $this->game_parts;$temp_count++) {
      $this_part_start = (($temp_count-1) * ($this->halftime + $parts_time));
      $this_part_end = $this_part_start + $parts_time;
      $next_part_start = $this_part_end + $this->halftime;
      if ($gone_since_begin >= $this_part_start && $gone_since_begin <= $this_part_end){
        $temptext = str_replace('%PART%', $temp_count, trim(htmlspecialchars($config['mark_now_playing_alt_actual_time'])));
        $temptext = str_replace('%MINUTE%', ($gone_since_begin+1 - ($temp_count-1)*$this->halftime),$temptext);
        break;
      }
      elseif ($gone_since_begin > $this_part_end && $gone_since_begin < $next_part_start) {
        $temptext = str_replace('%PART%', $temp_count, trim(htmlspecialchars($config['mark_now_playing_alt_actual_break'])));
        break;
      }
    }
    return $temptext;
  }
  function getPredictionConfig($use_admin = 0) {
    $query = "SELECT pp.*, p.auto_approve FROM #__joomleague_predictiongame_project AS pp
              LEFT JOIN #__joomleague_predictiongame AS p ON p.id = pp.prediction_id 
              WHERE pp.project_id = ".$this->id." AND p.published = 1";
    $this->_db->setQuery($query);
    if ($results = $this->_db->loadObjectList()) {
      $result = $results[0];
      foreach ($result AS $key=>$value) {
        if (!isset($this->$key)) $this->$key = $value;
      }
      if ($use_admin > 0) {
        $parent = new JLPrediction($this->_db);
        $parent->load($result->prediction_id);
        $this->tip_admin = $parent->getAdmins();
      } else $this->tip_admin = array();
    }
  }
  function usePrediction() {
    $query = "SELECT pp.id FROM #__joomleague_predictiongame_project AS pp
              LEFT JOIN #__joomleague_predictiongame AS p ON p.id = pp.prediction_id 
              WHERE pp.project_id = ".$this->id." AND p.published = 1";
    $this->_db->setQuery($query);
    if ($results = $this->_db->loadObjectList()) {
      return true;
    }
    else return false;
  }
  function getMatchEventsID($matchid, $teamid, $eventid = 0){
  	//function getMatchEvents($matchid){
  	$query="SELECT me.event_id
  	        FROM #__joomleague_match_events AS me
  	        LEFT JOIN #__joomleague_playertool AS pt ON pt.player_id = me.player_id
  	        WHERE me.match_id = $matchid AND pt.team_id = $teamid AND me.team_id = $teamid AND me.project_id = $this->id";
  	if ($eventid > 0) $query .= " AND me.event_type_id = $eventid";
  	$query .= " GROUP BY me.event_id ORDER BY me.event_type_id, me.event_time";
  	$this->_db->setQuery($query);
  	$result = $this->_db->loadObjectList();
  	return $result;
  }
  function areThereEvents ($match_id, $inout=1) {
	$inoutcount = 0;
	$query = "SELECT count(event_id) FROM #__joomleague_match_events
	           WHERE match_id = $match_id";
	$this->_db->setQuery($query);
	$countevents = $this->_db->loadResult();
	if ($inout) {
	  $query = "SELECT count(id) FROM #__joomleague_match_players WHERE match_id ='".$match_id."' AND came_in = 1";
	  $this->_db->setQuery($query);
      $inoutcount = $this->_db->loadResult();
	}
  	return ($countevents + $inoutcount);
  }
  
  function getPositions($id=0)
  {

  	$query="SELECT pos.id AS posid, posj.position_id AS id, pos.* FROM #__joomleague_positions AS pos
  	       LEFT JOIN #__joomleague_pos_joomleague AS posj ON pos.id = posj.position_id
  	       WHERE posj.project_id = ".$this->id;
  	if ($id > 0) $query.=" AND posj.position_id = $id";
  	$query.=" ORDER BY pos.ordering";
  	$this->_db->setQuery($query);
  	$result = $this->_db->loadObjectList("posid");
  	return $result;
  }

}
?>
